﻿using Oracle.DataAccess.Client;
using Platform.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace YXYLCommon
{
    /// <summary>
    /// 设置SQLSERVER数据库名,与webconfig中connectionstrings对应
    /// </summary>
    public enum eSQLDB
    {
        lccwjhys,
        lccwjhystest
    }
    /// <summary>
    /// 通用执行增删改存储过程
    /// </summary>
    public class CommonService
    {
        #region Constructor

        private CommonService()
        {
        }

        #endregion

        #region Property

        private DbHelper dbHelper;

        #endregion

        #region Initail

        //public static CommonService Ini(string conStr)
        //{
        //    CommonService commonService = new CommonService(); 
        //    commonService.dbHelper = new DbHelper(conStr);
        //    return commonService;
        //}

        public static CommonService Ini()
        {
            CommonService commonService = new CommonService();
            commonService.dbHelper = new DbHelper();
            return commonService;
        }
        public static CommonService IniSQL(string sqldb)
        {
            CommonService commonService = new CommonService();
            commonService.dbHelper = new DbHelper(sqldb);
            return commonService;
        }


        #endregion

        #region Method

        /// <summary>
        /// 执行增删改存储过程.存储过程返回格式为:select @ReturnNo=1,@ReturnMsg='操作成功!';
        /// </summary>
        public DbCmdResult ExcuteProc(DbProcObject sqlProcObject)
        {
            DbCmdResult rs = new DbCmdResult();
            sqlProcObject.AddParameterOut("@ReturnNo", 1, 1);
            sqlProcObject.AddParameterOut("@ReturnMsg", null, 50);
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];

            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }
            dbHelper.RunProcedureAUD(cmdText, CommandType.StoredProcedure, parms);
            rs.ReturnNo = Convert.ToInt32(parms[parms.Length - 2].Value);
            rs.ReturnMsg = Convert.ToString(parms[parms.Length - 1].Value);
            return rs;
        }
        /// <summary>
        /// 执行增删改存储过程.存储过程返回格式为:select @ReturnNo=1,@ReturnMsg='操作成功!';
        /// </summary>
        public DbCmdResult ExcuteProcOracle(DbProcObject sqlProcObject)
        {
            /*
            DbCmdResult rs = new DbCmdResult();
            sqlProcObject.AddParameterOut("V_RETURNNO", 1, 1);
            sqlProcObject.AddParameterOut("V_RETURNMSG", null, 500);
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];

            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }*/
            DbCmdResult rs = new DbCmdResult();
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(this.dbHelper.connectionString, sqlProcObject.ProcName);
            string cmdText = sqlProcObject.ProcName;
            for (int i = 0; i < storedParams.Length; i++)
            {
                for (int j = 0; j < sqlProcObject.ProcParameters.Count; j++)
                {
                    if (sqlProcObject.ProcParameters[j].ParameterName == storedParams[i].ParameterName)
                    {
                        storedParams[i].Value = sqlProcObject.ProcParameters[j].Value;
                        break;
                    }
                }
            }
            dbHelper.RunProcedureAUD(cmdText, CommandType.StoredProcedure, storedParams);
            rs.ReturnNo = Convert.ToInt32(storedParams[storedParams.Length - 2].Value);
            rs.ReturnMsg = Convert.ToString(storedParams[storedParams.Length - 1].Value);
            return rs;

        }

        /// <summary>
        /// 执行增删改存储过程.存储过程返回格式为:select @ReturnNo=1,@ReturnMsg='操作成功!';
        /// </summary>
        public DbCmdResult ExcuteProcSql(DbProcObject sqlProcObject)
        {
            DbCmdResult rs = new DbCmdResult();
            sqlProcObject.AddParameterOut("@ReturnNo", 1, 1);
            sqlProcObject.AddParameterOut("@ReturnMsg", null, 50);
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];

            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }
            dbHelper.RunProcedureAUD(cmdText, CommandType.Text, parms);
            rs.ReturnNo = Convert.ToInt32(parms[parms.Length - 2].Value);
            rs.ReturnMsg = Convert.ToString(parms[parms.Length - 1].Value);
            return rs;
        }
        public DbCmdResult ExcuteProcSqlList(List<DbProcObject> sqlProcObjectList)
        {
            DbCmdResult rs = new DbCmdResult();
            List<string> cmdtexts = new List<string>();
            List<DbParameter[]> parmsList = new List<DbParameter[]>();
            foreach (var item in sqlProcObjectList)
            {
                cmdtexts.Add(item.ProcName);
                item.AddSQLParameterOut("@ReturnNo", 1, 1);
                item.AddSQLParameterOut("@ReturnMsg", null, 50);
                DbParameter[] parms = new DbParameter[item.ProcParameters.Count];
                for (int i = 0; i < item.ProcParameters.Count; i++)
                {
                    parms[i] = item.ProcParameters[i];
                }                
                parmsList.Add(parms);
            }
            dbHelper.RunProcedureAUDTransaction(cmdtexts, parmsList);
            DbParameter[] lastParams= parmsList[parmsList.Count-1];
            rs.ReturnNo = Convert.ToInt32(lastParams[lastParams.Length-2].Value);
            rs.ReturnMsg = Convert.ToString(lastParams[lastParams.Length - 1].Value);
            return rs;

        }
        /*
        /// <summary>
        /// 返回单个结果
        /// </summary>
        public string GetSingleResult(DbProcObject sqlProcObject)
        {
            object obj = null;
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];
            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }
            using (DataTable table = dbHelper.RunProcedure(cmdText, parms))
            {
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];
                    obj = row[0];
                }
            }
            return Convert.ToString(obj);
        }
        */
        public string GetSingleResult(DbProcObject sqlProcObject)
        {
            object obj = null;
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(this.dbHelper.connectionString, sqlProcObject.ProcName);
            string cmdText = sqlProcObject.ProcName;
            for (int i = 0; i < storedParams.Length; i++)
            {
                for (int j = 0; j < sqlProcObject.ProcParameters.Count; j++)
                {
                    if (sqlProcObject.ProcParameters[j].ParameterName == storedParams[i].ParameterName)
                    {
                        storedParams[i].Value = sqlProcObject.ProcParameters[j].Value;
                        break;
                    }
                }
            }
            using (DataTable table = dbHelper.RunProcedureTran(cmdText, storedParams))
            {
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];
                    obj = row[0];
                }
            }
            return Convert.ToString(obj);
        }

        /// <summary>
        /// 执行查询结果为true或false
        /// 存储过程返回值大于0,表示true,小于0表示false
        /// </summary>
        public bool GetTureOrFalse(DbProcObject sqlProcObject)
        {
            sqlProcObject.AddParameterReturn();
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];
            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }
            dbHelper.RunProcedureAUD(cmdText, CommandType.StoredProcedure, parms);
            int no = Convert.ToInt32(parms[parms.Length - 1].Value);
            return (no > 0) ? true : false;
        }

        /// <summary>
        /// 执行查询结果为单列的列表数据
        /// </summary>
        public List<string> GetListString(DbProcObject sqlProcObject)
        {
            List<string> list = new List<string>();
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];
            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }
            using (DataTable table = dbHelper.RunProcedure(cmdText, parms))
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    list.Add(Convert.ToString(table.Rows[i][0]));
                }
            }
            return list;
        }


        /// <summary>
        /// 查询列表
        /// </summary>
        public List<T> GetList<T>(DbProcObject sqlProcObject) where T : BaseModel, new()
        {

            List<T> list = new List<T>();
            //string cmdText = sqlProcObject.ProcName;
            //DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];
            //for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            //{
            //    parms[i] = sqlProcObject.ProcParameters[i];
            //}
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(this.dbHelper.connectionString, sqlProcObject.ProcName);
            string cmdText = sqlProcObject.ProcName;
            for (int i = 0; i < storedParams.Length; i++)
            {
                for (int j = 0; j < sqlProcObject.ProcParameters.Count; j++)
                {
                    if (sqlProcObject.ProcParameters[j].ParameterName == storedParams[i].ParameterName)
                    {
                        storedParams[i].Value = sqlProcObject.ProcParameters[j].Value;
                        break;
                    }
                }
            }
            using (DataTable table = dbHelper.RunProcedureTran(cmdText, storedParams))
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    T obj = new T();
                    obj.ToObject(table.Rows[i]);
                    list.Add(obj);
                }
            }
            return list;
        }

        /// <summary>
        /// 查询列表(分页结果集)
        /// </summary>
        public List<T> GetPage<T>(BasePageInfo pageinfo) where T : BaseModel, new()
        {

            List<T> list = new List<T>();
            string cmdText = pageinfo.dbProcObject.ProcName;
            DbParameter[] parms = new DbParameter[pageinfo.dbProcObject.ProcParameters.Count];
            for (int i = 0; i < pageinfo.dbProcObject.ProcParameters.Count; i++)
            {
                parms[i] = pageinfo.dbProcObject.ProcParameters[i];
            }
            using (DataTable table = dbHelper.RunProcedure(cmdText, parms))
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    T obj = new T();
                    obj.ToObject(table.Rows[i]);
                    list.Add(obj);
                }
            }
            foreach (DbParameter item in pageinfo.dbProcObject.ProcParameters)
            {
                if (item.ParameterName == "@RecordsCount")
                {
                    pageinfo.RecordsCount = Convert.ToInt32(item.Value);
                }
            }
            return list;
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        public List<T> GetListBySql<T>(DbProcObject sqlProcObject) where T : BaseModel, new()
        {

            List<T> list = new List<T>();
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];
            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }
            using (DataTable table = dbHelper.RunSql(cmdText, parms))
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    T obj = new T();
                    obj.ToObject(table.Rows[i]);
                    list.Add(obj);
                }
            }
            return list;
        }


        /// <summary>
        /// 查询单个对象
        /// </summary>
        public T GetObject<T>(DbProcObject sqlProcObject) where T : BaseModel, new()
        {
            T obj = new T();
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(this.dbHelper.connectionString, sqlProcObject.ProcName);
            string cmdText = sqlProcObject.ProcName;
            for (int i = 0; i < storedParams.Length; i++)
            {
                for (int j = 0; j < sqlProcObject.ProcParameters.Count; j++)
                {
                    if (sqlProcObject.ProcParameters[j].ParameterName == storedParams[i].ParameterName)
                    {
                        storedParams[i].Value = sqlProcObject.ProcParameters[j].Value;
                        break;
                    }
                }
            }
            using (DataTable table = dbHelper.RunProcedure(cmdText, storedParams))
            {
                if (table.Rows.Count == 1)
                {
                    obj.ToObject(table.Rows[0]);
                }
                else
                {
                    obj.IsExists = false;
                }
            }
            return obj;
        }

        /// <summary>
        /// 查询单个对象
        /// </summary>
        public T GetObjectBySql<T>(DbProcObject sqlProcObject) where T : BaseModel, new()
        {
            T obj = new T();
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];
            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }
            using (DataTable table = dbHelper.RunSql(cmdText, parms))
            {
                if (table.Rows.Count == 1)
                {
                    obj.ToObject(table.Rows[0]);
                }
                else
                {
                    obj.IsExists = false;
                }
            }
            return obj;
        }

        /// <summary>
        /// 查询DATATABLE
        /// </summary>
        public DataTable GetDataTable(DbProcObject sqlProcObject)
        {
            string cmdText = sqlProcObject.ProcName;
            DbParameter[] parms = new DbParameter[sqlProcObject.ProcParameters.Count];
            for (int i = 0; i < sqlProcObject.ProcParameters.Count; i++)
            {
                parms[i] = sqlProcObject.ProcParameters[i];
            }
            return dbHelper.RunProcedureTran(cmdText, parms);
        }

        public DataSet GetOracleDataSet(DbProcObject sqlProcObject)
        {
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(this.dbHelper.connectionString, sqlProcObject.ProcName);
            string cmdText = sqlProcObject.ProcName;
            for (int i = 0; i < storedParams.Length; i++)
            {
                for (int j = 0; j < sqlProcObject.ProcParameters.Count; j++)
                {
                    if (sqlProcObject.ProcParameters[j].ParameterName == storedParams[i].ParameterName)
                    {
                        storedParams[i].Value = sqlProcObject.ProcParameters[j].Value;
                        break;
                    }
                }
            }
            return dbHelper.RunProcedureTranDS(cmdText, storedParams);
        }

        #endregion
    }


}

